package com.spoto.oa.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.spoto.oa.dao.ProductDao;
import com.spoto.oa.model.Product;
import com.spoto.oa.util.DB;

/**
 * 
 * @Project_name: OA
 * @Description: 产品信息表数据库操作DAO层的具体实现
 * @Author: LinYiSong
 * @Version:
 * @Date: 2010-9-23~2010-9-23
 */
public class ProductDaoImpl implements ProductDao {

	PreparedStatement pstm = null;
	ResultSet rs = null;

	public String getProductId(Product product) {
		String id = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select id from product  where code=?");
			pstm.setString(1, product.getCode());
			rs = pstm.executeQuery();
			id = rs.getString("id");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}

	public List<Product> getByName(String name) {
		name = "%" + name + "%";
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product  where name like ?");
			pstm.setString(1, name);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public List<Product> getByCompany(String company) {
		company = "%" + company + "%";
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product  where company like ?");
			pstm.setString(1, company);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public Product selectByCode(String code) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product where code=?");
			pstm.setString(1, code);
			rs = pstm.executeQuery();
			List<Product> products = resultSetToList(rs);
			return products.get(0);

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (java.lang.IndexOutOfBoundsException e) {
			return null;
		}
		return null;
	}

	public List<Product> getByCode(String code) {
		code = "%" + code + "%";
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product  where code like ?");
			pstm.setString(1, code);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public List<Product> getByType(String type) {
		type = "%" + type + "%";
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product  where type like ?");
			pstm.setString(1, type);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public void save(Product product) throws SQLException {
		pstm = DB.getConn().prepareStatement(
				"insert into product(code,name,date,creator,type,price,salesPrice,"
						+ "floatPrice,company,remarks) "
						+ "values(?,?,?,?,?,?,?,?,?,?)");
		pstm.setString(1, product.getCode());
		pstm.setString(2, product.getName());
		pstm.setDate(3, product.getDate());
		pstm.setString(4, product.getCreator());
		pstm.setString(5, product.getType());
		pstm.setFloat(6, product.getPrice());
		pstm.setFloat(7, product.getSalesPrice());
		pstm.setFloat(8, product.getFloatPrice());
		pstm.setString(9, product.getCompany());
		pstm.setString(10, product.getRemarks());
		pstm.executeUpdate();

	}

	public void update(int id, Product product) {
		try {
			pstm = DB
					.getConn()
					.prepareStatement(
							"update  product set code=?,name=?,date=?,creator=?,type=?,"
									+ "price=?,salesPrice=?,floatPrice=?,company=?,remarks=? where id=?");
			pstm.setString(1, product.getCode());
			pstm.setString(2, product.getName());
			pstm.setDate(3, product.getDate());
			pstm.setString(4, product.getCreator());
			pstm.setString(5, product.getType());
			pstm.setFloat(6, product.getPrice());
			pstm.setFloat(7, product.getSalesPrice());
			pstm.setFloat(8, product.getFloatPrice());
			pstm.setString(9, product.getCompany());
			pstm.setString(10, product.getRemarks());
			pstm.setInt(11, id);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(Product product) {
		try {
			pstm = DB.getConn().prepareStatement(
					"delete from product where code= ?");
			pstm.setString(1, product.getCode());
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 将ResutlSet封装成List
	 * 
	 * @param rs
	 * @return
	 */
	private List<Product> resultSetToList(ResultSet rs) {
		List<Product> products = new ArrayList<Product>();
		try {
			while (rs.next()) {
				Product product = new Product();
				product.setId(rs.getInt("id"));
				product.setCode(rs.getString("code"));
				product.setName(rs.getString("name"));
				product.setCompany(rs.getString("company"));
				product.setDate(rs.getDate("date"));
				product.setCreator(rs.getString("creator"));
				product.setType(rs.getString("type"));
				product.setRemarks(rs.getString("remarks"));
				product.setPrice(rs.getFloat("price"));
				product.setSalesPrice(rs.getFloat("salesPrice"));
				product.setFloatPrice(rs.getFloat("floatPrice"));
				products.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return products;

	}

	public List<Product> getAllProduct() {
		try {
			pstm = DB.getConn().prepareStatement("select * from product");
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public String addType(String value) {
		String msg = null;
		try {
			if (queryType(value)) {
				msg = "0";
			} else {
				pstm = DB.getConn().prepareStatement(
						"insert into product_type(type) values(?)");
				pstm.setString(1, value);
				pstm.executeUpdate();
				msg = "1";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return msg;
	}

	public void delType(String value) {
		try {
			pstm = DB.getConn().prepareStatement(
					"delete from product_type where type= ?");
			pstm.setString(1, value);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void editType(String oldVal, String newVal) {
		try {
			PreparedStatement pstm = DB.getConn().prepareStatement(
					"update product_type set type=? where type=?");
			pstm.setString(1, newVal);
			pstm.setString(2, oldVal);
			pstm.executeUpdate();
			//修改产品的对应类型
			pstm = DB.getConn().prepareStatement(
					"update product set type=? where type=?");
			pstm.setString(1, newVal);
			pstm.setString(2, oldVal);
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<String> getType() {
		List<String> types = new ArrayList<String>();
		try {
			pstm = DB.getConn().prepareStatement("select * from product_type");
			rs = pstm.executeQuery();
			while (rs.next()) {
				types.add((String) rs.getString("type"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return types;
	}

	public boolean queryType(String type) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select id from product_type where type=?");
			pstm.setString(1, type);
			rs = pstm.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 存导入数据时发生冲突的数据
	 */
	public void saveConfict(Product product) throws SQLException {
		pstm = DB.getConn().prepareStatement(
				"insert into product_conflict(code,name,date,creator,type,price,salesPrice,"
						+ "floatPrice,company,remarks) "
						+ "values(?,?,?,?,?,?,?,?,?,?)");
		pstm.setString(1, product.getCode());
		pstm.setString(2, product.getName());
		pstm.setDate(3, product.getDate());
		pstm.setString(4, product.getCreator());
		pstm.setString(5, product.getType());
		pstm.setFloat(6, product.getPrice());
		pstm.setFloat(7, product.getSalesPrice());
		pstm.setFloat(8, product.getFloatPrice());
		pstm.setString(9, product.getCompany());
		pstm.setString(10, product.getRemarks());
		pstm.executeUpdate();

	}

	/**
	 * 取得冲突的数据
	 */
	public Product selectConflictCode(String code) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product_conflict where code=?");
			pstm.setString(1, code);
			rs = pstm.executeQuery();
			List<Product> products = resultSetToList(rs);
			return products.get(0);

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (java.lang.IndexOutOfBoundsException e) {
			return null;
		}
		return null;
	}

	/**
	 * 删除冲突的员工数据
	 */
	public void deleteConflict(Product product) {
		try {
			pstm = DB.getConn().prepareStatement(
					"delete from product_conflict where code= ?");
			pstm.setString(1, product.getCode());
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 取得所有冲突的信息
	 * 
	 * @return
	 */
	public List<Product> getAllProductConflict() {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from product_conflict");
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

}
